package com.hnas.ecard.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.persistence.Query;

import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import com.hnas.core.dao.BaseDaoImpl;
import com.hnas.core.dao.PageBean;
import com.hnas.core.dto.DTOPagedList;
import com.hnas.core.util.DateUtil;
import com.hnas.core.util.StringUtil;
import com.hnas.ecard.dao.CardTrailDAO;
import com.hnas.ecard.domain.CardTrailStatus;
import com.hnas.ecard.domain.ECardCommonFinalValue;
import com.hnas.ecard.domain.ECardStatus;
import com.hnas.ecard.domain.SearchECardStatus;
import com.hnas.ecard.orm.CardTrail;

@Component("cardTrailDAO")
public class CardTrailDAOImpl extends BaseDaoImpl<CardTrail> implements CardTrailDAO {

	@Value("${send_card_time_limit}")
	private long sendCardTimeLimit;
	
	public CardTrailDAOImpl() {
		super(CardTrail.class);
	}

	@Override
	public List<CardTrail> findCardTrailByStatus(String accId, String phoneNo, String searchStatus, Integer pageNo, Integer pageSize) throws Exception {
		DTOPagedList<CardTrail> pageList = new DTOPagedList<CardTrail>();
		PageBean<CardTrail> page = new PageBean<CardTrail>();
		page.setPageNo(pageNo);
		page.setPageSize(pageSize);
		StringBuffer sb = new StringBuffer();
		List<Object> paras = new ArrayList<Object>();
		int j = 1;
		sb.append(" from CardTrail t where  t.isDeleted = 0 ");
		if (StringUtil.isNotEmpty(searchStatus)) {
			if (searchStatus.trim().equals(SearchECardStatus.RECEIVED)) {// 已收到:已领取，待领取
				if (StringUtil.isNotEmpty(accId) && StringUtil.isNotEmpty(phoneNo)) {
					sb.append(" and ((t.receiveAccId =  ? and t.status = '" + CardTrailStatus.CONFIRM_RECEIVE_GIVE + "') " + j++);
					paras.add(accId.trim());
					sb.append(" or (t.receivePhoneNo =  ? and t.status = '" + CardTrailStatus.RECEIVING_GIVING + "')) " + j++);
					paras.add(phoneNo.trim());
					sb.append(" order by t.status asc,t.createTime desc ");
				}
			} else if (searchStatus.trim().equals(SearchECardStatus.PRESENTED)) {// 已赠送:赠送中，已赠送，赠送被拒绝
				if (StringUtil.isNotEmpty(accId)) {
					sb.append(" and t.sendAccId =  ? " + j++);
					paras.add(accId.trim());
					sb.append(" and t.status =  ? " + j++);
					paras.add("'" + CardTrailStatus.RECEIVING_GIVING + "','" + CardTrailStatus.CONFIRM_RECEIVE_GIVE + "','" + CardTrailStatus.REFUSE + "'");
					sb.append(" order by t.createTime desc ");
				}
			}
		}
		page.setQuery(sb.toString());
		page.setQueryCount("select count(t.id) " + sb);
		this.queryByPage(page, paras.toArray());
		pageList.setPageNo(page.getPageNo());
		pageList.setPageSize(page.getPageSize());
		pageList.setTotalCount(page.getTotalCount());
		return page.getList();
	}

	@Override
	public CardTrail saveOrUpdate(CardTrail dto) throws Exception {
		CardTrail vo = null;
		if (null != dto) {
			if (StringUtil.isNotEmpty(dto.getId())) {
				vo = this.find(dto.getId());
			}

			if (null == vo) {
				vo = dto;
				vo.setCreateTime(DateUtil.dateTimeToString(new Date()));
				vo.setUpdateTime(DateUtil.dateTimeToString(new Date()));
				vo.setIsDeleted(false);
				this.persist(vo);
			} else {
				BeanUtils.copyProperties(dto, vo, new String[] { "id", "createTime" });
				vo.setUpdateTime(DateUtil.dateTimeToString(new Date()));
				this.merge(vo);
			}
		}
		return vo;
	}

	@Override
	public List<CardTrail> findCardsByStatus(String receiveAccId, String receivePhoneNo, String status) {
		String hql = " from CardTrail t where  t.isDeleted = 0 ";
		int index = 1;
		Map<Integer, String> map = new HashMap<Integer, String>();
		if (StringUtil.isNotEmpty(receiveAccId)) {
			hql += " and t.receiveAccId = ?";
			map.put(index++, receiveAccId);
		}
		if (StringUtil.isNotEmpty(receivePhoneNo)) {
			hql += " and t.receivePhoneNo = ?";
			map.put(index++, receivePhoneNo);
		}
		if (StringUtil.isNotEmpty(status)) {
			hql += " and t.status = ?";
			map.put(index++, status);
		}
		Query query = this.entityManager.createQuery(hql, CardTrail.class);
		if (!CollectionUtils.isEmpty(map)) {
			for (Entry<Integer, String> entry : map.entrySet()) {
				query.setParameter(entry.getKey(), entry.getValue());
			}
		}
		return query.getResultList();
	}

	@Override
	public List<CardTrail> searchCardTrailSql(CardTrail vo) {
		StringBuffer sb = new StringBuffer();
		List<Object> paras = new ArrayList<Object>();
		if (null != vo) {
			int j = 1;
			if (StringUtil.isNotEmpty(vo.getId())) {
				sb.append(" and t.id =?" + j++);
				paras.add(vo.getId().trim());
			}
			if (StringUtil.isNotEmpty(vo.getSendAccId())) {
				sb.append(" and t.sendAccId =?" + j++);
				paras.add(vo.getSendAccId().trim());
			}
			if (StringUtil.isNotEmpty(vo.getSendPhoneNo())) {
				sb.append(" and t.sendPhoneNo =?" + j++);
				paras.add(vo.getSendPhoneNo().trim());
			}
			if (StringUtil.isNotEmpty(vo.getSendName())) {
				sb.append(" and t.sendName =?" + j++);
				paras.add(vo.getSendName().trim());
			}
			if (StringUtil.isNotEmpty(vo.getSendCardNo())) {
				sb.append(" and t.sendCardNo =?" + j++);
				paras.add(vo.getSendCardNo().trim());
			}
			if (StringUtil.isNotEmpty(vo.getReceiveAccId())) {
				sb.append(" and t.receiveAccId =?" + j++);
				paras.add(vo.getReceiveAccId().trim());
			}

			if (StringUtil.isNotEmpty(vo.getReceivePhoneNo())) {
				sb.append(" and t.receivePhoneNo =?" + j++);
				paras.add(vo.getReceivePhoneNo().trim());
			}
			if (StringUtil.isNotEmpty(vo.getReceiveName())) {
				sb.append(" and t.receiveName =?" + j++);
				paras.add(vo.getReceiveName().trim());
			}
			if (StringUtil.isNotEmpty(vo.getStatus())) {
				sb.append(" and t.status =?" + j++);
				paras.add(vo.getStatus().trim());
			}
			if (null != vo.getIsDeleted()) {
				sb.append(" and t.isDeleted =?" + j++);
				paras.add(vo.getIsDeleted());
			}
		}
		if (sb.length() > 0) {
			sb.delete(0, 5);
			sb.insert(0, " from CardTrail t where ");
		} else {
			sb.append(" from CardTrail t");
		}
		sb.append(" order by t.createTime DESC");
		List<CardTrail> list = this.findByQuery(sb.toString(), paras.toArray());
		return list;
	}

	@Override
	public List<CardTrail> findCardTrailsByCardNoAndSomeStatus(String cardNo, String someStatus, Boolean isDeleted) {
		StringBuffer sb = new StringBuffer();
		List<Object> paras = new ArrayList<Object>();
			int j = 1;
			if (StringUtil.isNotEmpty(cardNo)) {
				sb.append(" and t.cardNo =?" + j++);
				paras.add(cardNo.trim());
			}
			if (StringUtil.isNotEmpty(someStatus)) {
				sb.append(" and t.status in (" + StringUtil.getChangeString(someStatus.trim())+ ")");
			}
			if (null == isDeleted) {
				sb.append(" and t.isDeleted =?" + j++);
				paras.add(isDeleted);
			}
		if (sb.length() > 0) {
			sb.delete(0, 5);
			sb.insert(0, " from CardTrail t where ");
		} else {
			sb.append(" from CardTrail t");
		}
		sb.append(" order by t.createTime DESC");
		List<CardTrail> list = this.findByQuery(sb.toString(), paras.toArray());
		return list;
	}

	@Override
	public List<CardTrail> listOverTime() {
		String sql = "select * from tbl_card_trail where is_deleted= ?1 and status=?2 and TIMESTAMPDIFF(SECOND,create_time,now()) >= ?3 order by create_time asc";
		Query query = this.entityManager.createNativeQuery(sql,CardTrail.class);
		query.setParameter(1, '0');
		query.setParameter(2, CardTrailStatus.RECEIVING_GIVING);
		query.setParameter(3, sendCardTimeLimit);
		return query.getResultList();
	}
	
	@Override
	public void autoReturnECard(String id, String id2) {
		String sql = "update tbl_acc_e_card set status = ?1,update_time=?2 where id = ?3 and status = ?4 and is_deleted=?5";
		String sql2 = "update tbl_card_trail set status = ?1,update_time=?2,sort=?3 where id = ?4 and status = ?5 and is_deleted=?6";
		Query query = this.entityManager.createNativeQuery(sql);
		query.setParameter(1, ECardStatus.NORMAL);
		query.setParameter(2, DateUtil.dateToString(new Date()));
		query.setParameter(3, id);
		query.setParameter(4, ECardStatus.GIVING);
		query.setParameter(5, false);
		Query query2 = this.entityManager.createNativeQuery(sql2);
		query2.setParameter(1, CardTrailStatus.OVERTIME);
		query2.setParameter(2, DateUtil.dateToString(new Date()));
		query2.setParameter(3, ECardCommonFinalValue.SORT_TAIL);
		query2.setParameter(4, id2);
		query2.setParameter(5, CardTrailStatus.RECEIVING_GIVING);
		query2.setParameter(6, false);
		
		query.executeUpdate();
		query2.executeUpdate();
	}

	@Override
	public List<CardTrail> listByCardAndDate(String cardNo, String date) {
		String sql = "select * from tbl_card_trail where send_card_no = ?1 and create_time > ?2 order by create_time asc";
		Query query = this.entityManager.createNativeQuery(sql, CardTrail.class);
		query.setParameter(1, cardNo);
		query.setParameter(2, date);
		return query.getResultList();
	}

	@Override
	public void autoReturnECardSaleForOther(String id) {
		String sql = "update tbl_card_trail set status = ?1,update_time=?2,sort=?3 where id = ?4 and status = ?5 and is_deleted=?6";
		Query query = this.entityManager.createNativeQuery(sql);
		query.setParameter(1, CardTrailStatus.OVERTIME);
		query.setParameter(2, DateUtil.dateToString(new Date()));
		query.setParameter(3, ECardCommonFinalValue.SORT_TAIL);
		query.setParameter(4, id);
		query.setParameter(5, CardTrailStatus.RECEIVING_GIVING);
		query.setParameter(6, false);
		
		query.executeUpdate();
	}

}
